/*DONE* Table [dbo].[<CompanyName>$Retail Sales Budget Name$5ecfc871-5d82-43f1-9c54-59685e82318d]  1/1  Script Date: 29.7.2020 13:04:06 ******/
/*Add prestaging table for one specific company*/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

DECLARE @CreateTable as VARCHAR(MAX)
DECLARE @Company as VARCHAR(100)
DECLARE @CompanyID as INT

SET @CompanyID = X --The X is the value that needs to be replaced with the [Company] ID from LSInsight$Companies
SET @Company = (SELECT LSInsight$Companies.[CompanyPrefix] from dbo.LSInsight$Companies WHERE [Company] = @CompanyID)

IF @Company IS NOT NULL
BEGIN

DECLARE @DropTable as VARCHAR(MAX)
SET @DropTable = 'DROP TABLE IF EXISTS [dbo].['+@Company+'$Retail Sales Budget Name$5ecfc871-5d82-43f1-9c54-59685e82318d]'
EXEC(@DropTable)

SET @CreateTable = 'CREATE TABLE [dbo].['+@Company+'$Retail Sales Budget Name$5ecfc871-5d82-43f1-9c54-59685e82318d](
	[timestamp] [timestamp] NOT NULL,
	[Name] [nvarchar](20) NOT NULL,
	[Description] [nvarchar](80) NOT NULL,
	[Budget Type] [int] NOT NULL,
	[Sales Start Date] [datetime] NOT NULL,
	[No_ of Periods] [int] NOT NULL,
	[Period Type] [int] NOT NULL,
	[Date Change Formula] [varchar](32) NOT NULL,
	[Multiplication Factor] [decimal](38, 20) NOT NULL,
	[Type Filter] [nvarchar](50) NOT NULL,
	[Budget Start Date] [datetime] NOT NULL,
	[Status] [int] NOT NULL,
	[Division Filter] [nvarchar](50) NOT NULL,
	[Item Category Filter] [nvarchar](50) NOT NULL,
	[Retail Product Filter] [nvarchar](100) NOT NULL,
	[Item Filter] [nvarchar](50) NOT NULL,
	[Vendor No_] [nvarchar](20) NOT NULL,
	[Store No_] [nvarchar](10) NOT NULL,
	[Planned Demand Type] [int] NOT NULL,
	[Rounding Type] [int] NOT NULL,
	[Calendar Type] [int] NOT NULL,
	[Allocation Rule Code] [nvarchar](20) NOT NULL,
	[Master Budget] [nvarchar](20) NOT NULL,
	[Hierarchy Code] [nvarchar](20) NOT NULL,
	[OTB View Code] [nvarchar](20) NOT NULL,
	[OTB Calculation Level] [int] NOT NULL,
	[OTB Check Purch_ Order] [tinyint] NOT NULL,
	[OTB Check Alloc_ Plan] [tinyint] NOT NULL,
	[OTB Calculation Depth (Hier_)] [int] NOT NULL,
	[OTB Calc_ with Scheduler] [tinyint] NOT NULL,
	[OTB Last Calculated (Date)] [datetime] NOT NULL,
	[OTB Last Calculated (Time)] [datetime] NOT NULL,
	[OTB Check Purch_ Order Level] [int] NOT NULL,
	[OTB Check Alloc_ Plan Level] [int] NOT NULL,
	[OTB Check PO Curr_ Period] [tinyint] NOT NULL,
	[OTB Check PO Until Curr Period] [tinyint] NOT NULL,
	[OTB Check PO Last Period] [tinyint] NOT NULL,
	[OTB Check AP Curr_ Period] [tinyint] NOT NULL,
	[OTB Check AP Until Curr Period] [tinyint] NOT NULL,
	[OTB Check AP Last Period] [tinyint] NOT NULL,
	[OTB PO Ignore Zero Budget Line] [tinyint] NOT NULL,
	[OTB AP Ignore Zero Budget Line] [tinyint] NOT NULL,
	[OTB Scheduler Job Type Code] [nvarchar](10) NOT NULL,
	[$systemId] [uniqueidentifier] NOT NULL,
	[Buyer ID] [nvarchar](50) NOT NULL,
	[User Buyer Group Code] [nvarchar](10) NOT NULL,
	[Owner Buyer ID] [nvarchar](50) NOT NULL,
	[Owner Buyer Group Code] [nvarchar](10) NOT NULL,
	[Last Modified On] [datetime] NOT NULL,
	[Last Modified by User] [nvarchar](50) NOT NULL,
	[Budget per Store] [tinyint] NOT NULL,
 CONSTRAINT ['+@Company+'$Retail Sales Budget Name$5ecfc871-5d82-43f1-9c54-59685e82318d$Key1] PRIMARY KEY CLUSTERED 
(
	[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT ['+@Company+'$Retail Sales Budget Name$5ecfc871-5d82-43f1-9c54-59685e82318d$$systemId] UNIQUE NONCLUSTERED 
(
	[$systemId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'

EXEC(@CreateTable)

END